<?php

class ImexController extends Controller {

    /**
     * @var string the default layout for the views. Defaults to '//layouts/column2', meaning
     * using two-column layout. See 'protected/views/layouts/column2.php'.
     */
    //public $layout='//layouts/column2';

    public $menu = [
        0 => ['label' => 'Список страниц', 'url' => ['index'],],
        10 => ['label' => 'Управление страницами', 'url' => ['admin'],],
        20 => ['label' => 'Создать страницу', 'url' => ['create'], 'prim' => true],
    ];

    /**
     * @return array action filters
     */
    public function filters() {
        return array(
            'accessControl', // perform access control for CRUD operations
            'postOnly + delete', // we only allow deletion via POST request
        );
    }

    /**
     * Specifies the access control rules.
     * This method is used by the 'accessControl' filter.
     * @return array access control rules
     */
    public function accessRules() {
        return array(
            array('allow', // allow all users to perform 'index' and 'view' actions
                'actions' => array('index', 'view', 'import'),
                'users' => array('@'),
            ),
            array('allow', // allow authenticated user to perform 'create' and 'update' actions
                'actions' => array('create', 'update'),
                'users' => array('@'),
            ),
            array('allow', // allow admin user to perform 'admin' and 'delete' actions
                'actions' => array('admin', 'delete'),
                'users' => array('@'),
            ),
            array('deny', // deny all users
                'users' => array('*'),
            ),
        );
    }

    public function actionImport() {
        if (isset($_POST['importedFile']) && Yii::app()->request->isAjaxRequest) {
//            $response = $_POST['maxim'] ?
//                    $this->importMaksimFormat(Yii::app()->basePath . '/../uploads/imex/import/' . $_POST['importedFile']) :
//                    $this->importCSV(Yii::app()->basePath . '/../uploads/imex/import/' . $_POST['importedFile']);
            $response = $this->importMaksimFormat(Yii::app()->basePath . '/../uploads/imex/import/' . $_POST['importedFile']);
            echo json_encode($response);
            Yii::app()->end();
        }


        Yii::import("ext.EAjaxUpload.qqFileUploader");

        $folder = 'uploads/imex/import/'; // folder for uploaded files
        $allowedExtensions = array("xls", "xlsx", "csv"); //array("jpg","jpeg","gif","exe","mov" and etc...
        $sizeLimit = 100 * 1024 * 1024; // maximum file size in bytes

        $uploader = new qqFileUploader($allowedExtensions, $sizeLimit);
        $result = $uploader->handleUpload($folder);
//        print_r($result);exit;
        $return = htmlspecialchars(json_encode($result), ENT_NOQUOTES);

        $fileSize = filesize($folder . $result['filename']); //GETTING FILE SIZE
        $fileName = $result['filename']; //GETTING FILE NAME

        echo $return; // it's array
    }

    public function importCSV($file) {
        ini_set("memory_limit", "1024M");
        set_time_limit(0);

        $array = $this->csv_to_array($file);
        $services = explode(';', $array[2][0]);
        for ($n = 0; $n <= 4; $n++)
            unset($services[$n]);

        foreach ($services as $key => $value) {
            if (filter_var($value, FILTER_VALIDATE_INT))
                $mainServices[$value] = $key - 1;
        }
        $counter = 0;
        for ($n = 3; $n < sizeof($array); $n++) {
            if ($counter++ % 100 == 0) {
                sleep(2);
            }
            $mprices = explode(';', $array[$n][0]);
            $modelId = $mprices[0];
            $modelServices = [];
            foreach ($mainServices as $service => $cell) {
                $modelServices[$service] = ['price' => $mprices[$cell], 'avail' => $mprices[$cell + 1]];
            }
            foreach ($modelServices as $service => $value) {
                try {
                    $encoding = mb_detect_encoding($value['price']);
                    $p = filter_var($value['price'], FILTER_VALIDATE_INT) ? (int) $value['price'] : iconv("WINDOWS-1251", "UTF-8//IGNORE", $value['price']);
                    $a = filter_var($value['avail'], FILTER_VALIDATE_INT) ? (int) $value['avail'] : iconv("WINDOWS-1251", "UTF-8//IGNORE", $value['avail']);
                    if (!in_array($a, ['0', '1', '2']))
                        $a = 0;

                    if (trim($value['price']) == '') {
                        // delete
                        $sql = " DELETE FROM {{_model2serv}} WHERE \"modelId\"={$modelId} AND \"serviceId\"=" . $service . ";";
                    } else {
                        $sql = "select \"newLink\"({$modelId}::int8, {$service}::int2, '" . $p . "'::varchar, " . $a . "::int2, 0::int2);";
                    }
                    Yii::app()->db->createCommand($sql)->execute();
                } catch (Exception $ex) {
                    return ['response' => 'error', 'desc' => 'Произошла ошибка при обработки данных. Проверьте правильность данных в привязки услуги: ' . $service . ' к модели: ' . $modelId];
                }
            }
        }

        return ['response' => 'success', 'desc' => 'Поздравляем, на основе загруженного Вами файла данные успешно обновлены ! <a href="' . Yii::app()->createUrl('admin/model2serv/admin') . '"><strong>Просмотр обновленных данных<strong></a> '];
    }

    public function importMaksimFormat($file) {

        spl_autoload_unregister(array('YiiBase', 'autoload'));

        $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');
        include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

        spl_autoload_register(array('YiiBase', 'autoload'));

        $excel = PHPExcel_IOFactory::load($file); // PHPExcel
        $sheets = $excel->getAllSheets();
        foreach ($sheets as $sheet) { // PHPExcel_Worksheet
            $array[] = $sheet->toArray();

        }
//        print_r($array);exit;
//        $array = $this->csv_to_array($file);
//        echo '<pre>';
//        print_r($array);
//        exit;
        try {
            unset($array[0][0]);
            foreach ($array[0] as $key => $value) {
                $sql = 'select "newLink"(' . $value[0] . '::int8,' . $value[1] . '::int2,\'' . $value[3] . '\'::varchar,' . $value[4] . '::int2,' . $value[2] . '::int2);';
                //$sql = 'update mb__price2item set "priceAll" = \'' . $value[3] . '\' from mb__model2serv where "itemType"=\'link\' and "itemId"=mb__model2serv."linkId" and mb__model2serv."modelId"=' . $value[0] . ' and mb__model2serv."serviceId"=' . $value[1] . ' and "priceRegionId"=' . $value[2] . '';
                
                Yii::app()->db->getCommandBuilder()->createSqlCommand($sql)->execute();
                
            }
        } catch (Exception $ex) {
            return ['response' => 'error', 'desc' => 'Произошла ошибка при обработки данных. Проверьте правильность данных в привязки услуги: ' . $value[1] . ' к модели: ' . $value[0]];
        }
        return ['response' => 'success', 'desc' => 'Поздравляем, на основе загруженного Вами файла данные успешно обновлены ! <a href="' . Yii::app()->createUrl('admin/model2serv/admin') . '"><strong>Просмотр обновленных данных<strong></a> '];
    }

    public function csv_to_array($filename = '', $delimiter = ',') {
        if (!file_exists($filename) || !is_readable($filename))
            return FALSE;
        $data = array();
        $file = fopen($filename, 'r');
        while (($line = fgetcsv($file)) !== FALSE) {
            $data[] = $line;
        }
        fclose($file);
        return $data;
    }

    /**
     * Lists all models.
     */
    public function actionIndex() {
        set_time_limit(0);

        $download = false;
//		echo "<pre>";
//			print_r( $_POST );
//		echo "</pre>";

        if (!empty($_POST['datas']) && $_POST['format'] == 'excel2007') {
            $download = $this->exportExcel($_POST['datas']);
        } else if (!empty($_POST['datas']) && $_POST['format'] == 'csv') {
            $download = $this->exportCSV($_POST['datas']);
        }

        //exit;

        $sql = "   SELECT	c.\"pageId\" AS \"categoryId\",	c.\"pageTitle\" AS \"categoryTitle\",
			b.\"pageId\" AS \"brandId\", b.\"pageTitle\" AS \"brandTitle\",b.\"pageSort\"
				     FROM {{pages}} c
				LEFT JOIN {{pages}} b ON (	b.\"pageParent\" = c.\"pageId\"	AND b.\"pageType\" = 'brand')
				WHERE c.\"pageType\" = 'category' ORDER BY c.\"pageId\", b.\"pageSort\";";
        $all = Yii::app()->db->getCommandBuilder()->createSqlCommand($sql)->queryAll();

        $result = [];

        foreach ($all as $row) {
            $this->addCategory($result, $row);
        }
        foreach ($result as $key => $cats) {
            $services = Yii::app()->db->createCommand(" SELECT \"serviceId\", \"serviceTitle\" FROM {{services}} WHERE \"serviceCategory\"=" . $cats['id'])->queryAll(TRUE);
            $result[$key]['services'] = $services;
        }

        $this->render('index', array(
            'cats' => $result,
            'download' => $download,
            'import' => (isset($response) && $response['response'] == 'error') ? $response['desc'] : false
        ));
    }

    protected function addCategory(&$result, $row) {
        $found = false;
        foreach ($result as $key => $cats) {
            if ($cats['id'] == $row['categoryId']) {
                $found = true;
                $this->addBrand($result[$key], $row);
            }
        }
        if (!$found) {
            $newCat = ['id' => $row['categoryId'], 'title' => $row['categoryTitle']];
            $this->addBrand($newCat, $row);
            $result[] = $newCat;
        }
    }

    protected function addBrand(&$cat, $row) {
        if (empty($row['brandId'])) {
            return;
        }
        $found = false;
        if (isset($cat['brands'])) {
            foreach ($cat['brands'] as $key => $brands) {
                if ($brands['id'] == $row['brandId']) {
                    $found = true;
                    $this->addModel($cat['brands'][$key], $row);
                }
            }
        } else {
            $cat['brands'] = false;
        }
        if (!$found) {
            $newBrand = ['id' => $row['brandId'], 'title' => $row['brandTitle']];
            $this->addModel($newBrand, $row);
            $cat['brands'][] = $newBrand;
        }
    }

    protected function addModel(&$brand, $row) {
        if (empty($row['modelId'])) {
            return;
        }
        $found = false;
        if (isset($brand['models'])) {
            foreach ($brand['models'] as $model) {
                if ($model['id'] == $row['modelId']) {
                    $found = true;
                }
            }
        } else {
            $brand['models'] = false;
        }
        if (!$found) {
            $brand['models'][] = ['id' => $row['modelId'], 'title' => $row['modelTitle']];
        }
    }

    protected function exportCSV($datas) {
        ini_set("memory_limit", "1024M");
        set_time_limit(0);

        $cats = array();
        $p_models = array();
        $modelIDS = [];

        $path = Yii::app()->basePath . '/../uploads/imex/';
        $download = 'mobi03_' . date("d-m-Y_H-i-s", time()) . ".csv";
        $file = $path . $download;

        foreach ($datas as $key => $value) {
            $cat = ['id' => explode("#", $key)[1]];
            if (isset($value['services']) && sizeof($value['services']) > 0) {
                $pServs = [];
                foreach ($value['services'] as $sKey => $sValue) {
                    $serv = explode("#", $sKey);
                    $pServs[] = ['id' => $sValue, 'title' => $serv[0]];
                }
                $cat['services'] = $pServs;
            }
            $cats[] = $cat;
            unset($value['services']);

            foreach ($value as $brand => $models) {
                if (!is_array($models)) {
                    $sql = "    select \"pageTitle\", \"pageId\", \"pageUrl\" FROM {{pages}} WHERE \"pageParent\"=" . $models;
                    $models = Yii::app()->db->createCommand($sql)->queryAll(true);
                }
                if (is_array($models)) {
                    foreach ($models as $id => $model) {
                        $p_models[] = array(
                            'catId' => explode("#", $key)[1],
                            'catTitle' => explode("#", $key)[0],
                            'brandId' => explode("#", $brand)[1],
                            'brandTitle' => explode("#", $brand)[0],
                            'modelId' => $model['pageId'],
                            'modelTitle' => $model['pageTitle']
                        );
                        $modelIDS[] = $model['pageId'];
                    }
                }
            }
        }

        $this->writetoCSV($file, ['Экспорт; данных с сайта; mobi03.ru на; состояние; ' . date("d.m.Y года H:i:s", time())]);
        $this->writetoCSV($file, []);
        $line = ['ID модели', 'Категория', 'Производитель', 'Модель'];
        $serviceIDS = [];
        $_cats = [];
        foreach ($cats as $cKey => $category) {
            if (isset($category['services']) && sizeof($category['services']) > 0) {
                $_tempServices = [];
                foreach ($category['services'] as $sKey => $service) {
                    $serviceIDS[] = $service['id'];
                    $line[] = $service['title'];
                    $line[] = $service['id'];
                    $_tempServices[$service['id']] = sizeof($line) - 2;
                }
                unset($cats[$cKey]['services']);
                $cats[$cKey]['services'] = $_tempServices;
            }
            $_cats[$cats[$cKey]['id']] = $cats[$cKey];
        }
        $cats = $_cats;
        unset($_cats);
        $this->writetoCSV($file, $line);

        if (sizeof($modelIDS) < 1 || sizeof($serviceIDS) < 1) {
            return;
        }

        $sql = "  SELECT ( SELECT p.\"priceAll\" || '#' || p.\"priceAvailable\" FROM {{_price2item}} p WHERE p.\"itemType\" = 'link' AND p.\"itemId\" = ms.\"linkId\" AND p.\"priceRegionId\" = 1 ORDER BY p.\"priceChangeDate\" DESC LIMIT 1 ) AS price, ms.\"serviceId\", ms.\"modelId\" FROM {{_model2serv}} ms WHERE ms.\"serviceId\" IN(  " . implode(",", $serviceIDS) . ") AND ms.\"modelId\" IN (" . implode(",", $modelIDS) . ")";

        $priceList = Yii::app()->db->createCommand($sql)->queryAll(true);
        $counter = 0;
        foreach ($p_models as $model) {
            $line = [];
            $line[] = $model['modelId'];
            $line[] = $model['catTitle'];
            $line[] = $model['brandTitle'];
            $line[] = $model['modelTitle'];
            $modelPriceList = $this->findPrice($priceList, $model['modelId']);
            if ($modelPriceList) {
                foreach ($modelPriceList as $lPrice) {
                    $k = explode("#", $lPrice['price']);
                    if (sizeof($k) < 2) {
                        echo '<span style="background-color:red;">ERROR:';
                        print_r($modelPriceList);
                        echo '</span>';
                    } else {
                        list($modelPrice, $modelAvail) = $k;
                        if (!isset($cats[$model['catId']]['services'][(int) $lPrice['serviceId']])) {
                            
                        } else {
                            $line[$cats[$model['catId']]['services'][(int) $lPrice['serviceId']]] = $modelPrice;
                            $line[$cats[$model['catId']]['services'][(int) $lPrice['serviceId']] + 1] = $modelAvail;
                        }
                    }
                }
            }

            $_tLine = [];

            for ($i = 0; $i <= max(array_keys($line)); $i++) {
                if (!isset($line[$i])) {
                    $_tLine[] = ' ';
                } else {
                    $_tLine[] = $line[$i];
                }
            }

            $this->writetoCSV($file, $_tLine);

            if ($counter++ % 200 == 0) {
                sleep(3);
            }
        }
        return Yii::app()->request->getBaseUrl(true) . '/uploads/imex/' . $download;
    }

    protected function writetoCSV($file, $line) {
        $file = fopen($file, "a");
        fwrite($file, iconv("UTF-8", "WINDOWS-1251", implode(";", $line) . ";\r\n"));
        fclose($file);
    }

    protected function findPrice(&$priceList, $model) {
        $result = [];
        foreach ($priceList as $price) {
            if ($price['modelId'] == $model) {
                $result[] = $price;
            }
        }
        return sizeof($result) > 0 ? $result : false;
    }

    protected function exportExcel($datas) {
        ini_set("memory_limit", "1024M");

        $exportAvailable = ( isset($datas) && $datas['exportAvailable'] == 'on') ? true : false;
        if ($exportAvailable) {
            unset($datas['exportAvailable']);
        }
        $cats = array();
        $p_models = array();
        $modelIDS = [];
        foreach ($datas as $key => $value) {
            $cat = ['id' => explode("#", $key)[1]];
            if (isset($value['services']) && sizeof($value['services']) > 0) {
                $pServs = [];
                foreach ($value['services'] as $sKey => $sValue) {
                    $serv = explode("#", $sKey);
                    $pServs[] = ['id' => $sValue, 'title' => $serv[0]];
                }
                $cat['services'] = $pServs;
            }
            $cats[] = $cat;
            unset($value['services']);

            foreach ($value as $brand => $models) {
                if (is_array($models)) {
                    foreach ($models as $id => $model) {
                        $p_models[] = array(
                            'catId' => explode("#", $key)[1],
                            'catTitle' => explode("#", $key)[0],
                            'brandId' => explode("#", $brand)[1],
                            'brandTitle' => explode("#", $brand)[0],
                            'modelId' => $model,
                            'modelTitle' => $id
                        );
                        $modelIDS[] = $model;
                    }
                }
            }
        }

        spl_autoload_unregister(array('YiiBase', 'autoload'));

        $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');
        include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

        spl_autoload_register(array('YiiBase', 'autoload'));

        $path = Yii::app()->basePath . '/../uploads/imex/';

        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_discISAM;
        $cacheSettings = array('dir' => $path);
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $objPHPexcel = PHPExcel_IOFactory::load($path . '_blank.xlsx');
        $objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
        $objWorksheet->setTitle("Цена общая");
        $objWorksheet->getCell("C4")->setValue(date('d.m.Y', time()) . ' года, в ' . date('H:i:s', time()));
        $objWorksheet->getCell("C5")->setValue(Yii::app()->user->Name['userName']);

        foreach ($p_models as $key => $model) {
            $objWorksheet->getCell("A" . ($key + 10))->setValue($model['modelId']);
            $objWorksheet->getCell("B" . ($key + 10))->setValue($model['catTitle']);
            $objWorksheet->getCell("C" . ($key + 10))->setValue($model['brandTitle']);
            $objWorksheet->getCell("D" . ($key + 10))->setValue($model['modelTitle']);
        }

        foreach ($cats as $category) {
            if (isset($category['services']) && sizeof($category['services']) > 0) {
                $sCounter = 0;
                foreach ($category['services'] as $service) {
                    $objWorksheet->getCell($this->getCell($sCounter * 2 + 5) . '7')->setValue($service['title']);
                    $objWorksheet->getCell($this->getCell($sCounter * 2 + 6) . '7')->setValue($service['id']);
                    $objWorksheet->getCell($this->getCell($sCounter * 2 + 5) . '8')->setValue('Цена');
                    $objWorksheet->getCell($this->getCell($sCounter * 2 + 6) . '8')->setValue('Наличие');

                    if ($sCounter != 0) {
                        $objWorksheet->duplicateStyle($objWorksheet->getStyle('E7:F8'), $this->getCell($sCounter * 2 + 5) . '7:' . $this->getCell($sCounter * 2 + 6) . '8');
                    }
                    $sCounter++;
                }
            }
        }
        foreach ($cats as $category) {
            if (isset($category['services']) && sizeof($category['services']) > 0) {
                $sCounter = 0;

                foreach ($category['services'] as $service) {
                    $sql = "  SELECT( SELECT p.\"priceAll\" || '#' || p.\"priceAvailable\" FROM {{_price2item}} p WHERE p.\"itemType\" = 'link' AND p.\"itemId\" = ms.\"linkId\" AND p.\"priceRegionId\" = 1 ORDER BY p.\"priceChangeDate\" DESC LIMIT 1 ) AS price, ms.\"modelId\" FROM {{_model2serv}} ms WHERE ms.\"serviceId\" =  " . $service['id'] . " AND ms.\"modelId\" IN (" . implode(",", $modelIDS) . ")";

                    $prices = Yii::app()->db->createCommand($sql)->queryAll(TRUE);

                    if ($prices) {
                        foreach ($prices as $price) {
                            $position = $this->getModelCell($p_models, $price['modelId']);
                            if ($position) {
                                list($priceDb, $availDb) = explode("#", $price['price']);
                                $objWorksheet->getCell($this->getCell($sCounter * 2 + 5) . $position)->setValue($priceDb);
                                $objWorksheet->getCell($this->getCell($sCounter * 2 + 6) . $position)->setValue($availDb);
                            }
                        }
                    }
                    $sCounter++;
                }
            }
        }

        // клонирование листа для регионов
        foreach (Yii::app()->params['regions'] as $region) {
            $newSheet = clone $objPHPexcel->getSheetByName("Цена общая");
            $newSheet->setTitle($region['regionTitle']);
            foreach ($cats as $category) {
                if (isset($category['services']) && sizeof($category['services']) > 0) {
                    $sCounter = 0;
                    foreach ($category['services'] as $service) {
                        $sql = "  SELECT( SELECT p.\"priceSum\" || '#' || p.\"priceAvailable\" FROM {{_price2item}} p WHERE p.\"itemType\" = 'link' AND p.\"itemId\" = ms.\"linkId\" AND p.\"priceRegionId\" = " . $region['regionId'] . " ORDER BY p.\"priceChangeDate\" DESC LIMIT 1 ) AS price, ms.\"modelId\" FROM {{_model2serv}} ms WHERE ms.\"serviceId\" =  " . $service['id'] . " AND ms.\"modelId\" IN (" . implode(",", $modelIDS) . ")";
                        $prices = Yii::app()->db->createCommand($sql)->queryAll(TRUE);
                        if ($prices) {
                            foreach ($prices as $price) {
                                $position = $this->getModelCell($p_models, $price['modelId']);
                                if ($position) {
                                    list($priceDb, $availDb) = explode("#", $price['price']);
                                    $newSheet->getCell($this->getCell($sCounter * 2 + 5) . $position)->setValue($priceDb);
                                    $newSheet->getCell($this->getCell($sCounter * 2 + 6) . $position)->setValue($availDb);
                                }
                            }
                        }
                    }
                }
            }

            $objPHPexcel->addSheet($newSheet, $region['regionId']);
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
        $download = 'mobi03_' . date("d-m-Y-H-i-s", time()) . ".xls";
        $objWriter->save($path . $download);

        return Yii::app()->request->getBaseUrl(true) . '/uploads/imex/' . $download;
    }

    protected function getModelCell(&$models, $model) {
        foreach ($models as $key => $pmodel) {
            if ((int) $pmodel['modelId'] === (int) $model) {
                return $key + 10;
            }
        }
        return false;
    }

    protected function getCell($number) {
        if ($number == 0) {
            return "Z";
        }
        if ($number <= 26) {
            return chr($number + 64);
        } else {
            return $this->getCell(intval(($number / 26))) . $this->getCell($number % 26);
        }
    }

    protected function Import($file) {
        if (!file_exists($file)) {
            return ['response' => 'error', 'desc' => "Загруженный Вами файл {$file} не существует на сервере, пожалуйста попробуйте загрузить его заново. А также, убедитесь что в названии не участвуют пробел, буквы на кириллице, а также непринятые символы как *,/,$,%,^,& и т.д."];
        }
        spl_autoload_unregister(array('YiiBase', 'autoload'));

        $phpExcelPath = Yii::getPathOfAlias('ext.phpexcel');

        include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');

        spl_autoload_register(array('YiiBase', 'autoload'));

        $objPHPexcel = PHPExcel_IOFactory::load($file);
        $objPHPexcel->setActiveSheetIndex(0);
        $aSheet = $objPHPexcel->getActiveSheet();

        $titles = $services = [];
        $insertQuery = "";

        $transaction = Yii::app()->db->beginTransaction();
        foreach ($aSheet->getRowIterator() as $rowindex => $row) {
            if ($rowindex < 10 && $rowindex != 7 && $rowindex != 8)
                continue;

            $cellIterator = $row->getCellIterator();
            $item = [];

            foreach ($cellIterator as $key => $cell) {
                $value = $cell->getCalculatedValue();

                if ($rowindex == 7) {
                    if (is_float($value * 1) && $value * 1 > 0) {
                        $services[$key] = $services[$key - 1] = $services[$key - 2] = $services[$key - 3] = $services[$key - 4] = $value;
                    }
                }
                if ($rowindex == 8 && is_string($value) && $value != "") {
                    $titles[$key] = $titles[$key + 1] = $titles[$key + 2] = $titles[$key + 3] = $titles[$key + 4] = $value;
                }
                $item[$key] = $value;
            }

            if ($rowindex < 10 || sizeof($item) < 5)
                continue;

            $model = [
                'id' => $item[0] * 1,
                'category' => $item[1],
                'brand' => $item[2],
                'title' => $item[3],
            ];

            if (!is_float($model['id']) || $model['id'] < 1) {
                return ['response' => 'error', 'desc' => 'Ошибка в правильности данных, проверьте значение первого столбца ID. Модель ID:' . $model['id']];
            }

            $servicesCount = ( sizeof($item) - 4) / 5;

            if (!is_int($servicesCount)) {
                return ['response' => 'error', 'desc' => 'Формат ячеек либо количество проставленных значений не совпадает. Модель ID:' . $model['id']];
            }

            foreach ($item as $index => $value) {
                if ($index < 4)
                    continue;
                if ($index % 5 === 0) {
                    $model['services'][] = [
                        'id' => $services[$index],
                        'title' => $titles[$index],
                        'price' => $item[$index - 1],
                        'block' => $value,
                        'available' => $item[$index + 1],
                        'sort' => $item[$index + 2],
                        'active' => $item[$index + 3]
                    ];
                }
            }

            if (isset($model['services']) && sizeof($model['services'])) {
                try {
                    foreach ($model['services'] as $service) {
                        if (strtoupper(trim($service['price'])) == "D") {
                            $sql = "DELETE FROM {{_price2item}} WHERE itemType='link' AND itemId = (SELECT linkId FROM {{_model2serv}} WHERE modelId={$model['id']} AND serviceId={$service['id']});";
                            Yii::app()->db->getCommandBuilder()->createSqlCommand($sql)->execute();
                            $sql = "DELETE FROM {{_model2serv}} WHERE modelId={$model['id']} AND serviceId={$service['id']};";
                            Yii::app()->db->getCommandBuilder()->createSqlCommand($sql)->execute();
                        } else {
                            $sql = " INSERT INTO {{_model2serv}} VALUES(null, {$model['id']}, {$service['id']}, {$service['block']},{$service['sort']}, {$service['available']},{$service['active']}) 
								   ON DUPLICATE KEY UPDATE blockNo={$service['block']}, sort = {$service['sort']}, available= {$service['available']}, active = {$service['active']}";
                            Yii::app()->db->getCommandBuilder()->createSqlCommand($sql)->execute();

                            $priceSum = Yii::app()->db->getCommandBuilder()->createSqlCommand("SELECT pi.priceSum From {{_price2item}} pi WHERE pi.itemType='link' and pi.itemId = (select ms.linkId FROM {{_model2serv}} ms WHERE ms.modelId={$model['id']} AND ms.serviceId={$service['id']}) ORDER BY pi.priceChangeDate DESC Limit 1")->queryRow();
                            if (!$priceSum || $priceSum['priceSum'] * 1 != $service['price']) {
                                Yii::app()->db->getCommandBuilder()->createSqlCommand(" INSERT INTO {{_price2item}} SELECT 'link', ms.linkId, " . time() . ",{$service['price']} FROM {{_model2serv}} ms WHERE ms.modelId={$model['id']} AND ms.serviceId={$service['id']}")->execute();
                            }
                        }
                    }
                } catch (Exception $ex) {
                    $transaction->rollback();
                    return ['response' => 'error', 'desc' => 'Произошла непредвиденная ошибка при работе с Базой Данных, повторите пожалуйста операцию через некоторое время.<br /><strong><font color="red">' . print_r($e->errorInfo[2], 1) . "</font></strong>"];
                }
            }
        }
        $transaction->commit();
        return ['response' => 'success', 'desc' => 'Поздравляем, на основе загруженного Вами файла данные успешно обновлены ! <a href="' . Yii::app()->createUrl('admin/model2serv/admin') . '"><strong>Просмотр обновленных данных<strong></a> '];
    }

}
